﻿####
## get-DisconnectedMailboxes.ps1
## David Ray - August 25, 2010
## Script produces a list of disconnected mailboxes on Exchange 2003 and 2007 servers
## Included is date of disconnect and a caculation of how many days have passed since disconnect
## Also included is whether or not another mailbox exists for the user, indicating that the mailbox was moved rather than deleted
##
## Requires Excel on local machine (tested in Excel 2007)

## Configuration variables
$date = Get-Date
$pathForReport = "E:\EVArchiveData\AlternateDailyReports"  #path for the excel report
$nameForReport = "DisconnectedMailboxes-$($date.year)-$('{0:D2}' -f $date.month)-$('{0:D2}' -f $date.day)" # filename of report
## A list of Exchange 2003 servers.  Number will be append to 'CHW-MSG-'
$2003Servers = ("301","302","303","401","402","403")
## A list of Exchange 2007 servers.  Number will be append to 'CHW-MSG-'
$2007Servers = ("011","821","822","823","824")

cls
## Use WMI to get the 2003 disconnected mailboxes
$disc03 = $2003Servers | ForEach-Object {$step=0} {Write-Progress -Activity "Getting 2003 disconnected mailboxes" -Status "% Complete" -PercentComplete (($step / 6) * 100) -Id 2 -CurrentOperation "Querying CHW-MSG-$($_)"; `
 Get-Wmiobject -namespace root\MicrosoftExchangeV2 -class Exchange_Mailbox -computer "chw-msg-$($_)" -filter "DateDiscoveredAbsentInDS is not null"; $step += 1 } | `
 Select-Object @{Name="DisplayName";Expression={$_.MailboxDisplayName}}, ServerName, StorageGroupName, @{Name="DatabaseName";Expression={$_.StoreName}}, @{Name="TotalItemSize";Expression={$_.Size}}, @{Name="ItemCount";Expression={$_.TotalItems}}, @{Name="DisconnectDate";Expression={[Management.ManagementDateTimeConverter]::ToDateTime($_.DateDiscoveredAbsentInDS)}}, LegacyDN
 Write-Progress -Activity "Getting 2003 disconnected mailboxes" -Status "% Complete" -PercentComplete (($step / 6) * 100) -Id 2 
 
 ## Use Get-MailboxStatistics to get 2007 disconnected mailboxes
$disc07 = $2007Servers | ForEach-Object {$step=0 } {Write-Progress -Activity "Getting 2007 disconnected mailboxes"  -Status "% Complete" -PercentComplete (($step / 5) * 100) -Id 3 -CurrentOperation "Querying CHW-MSG-$($_)" ; `
 Get-MailboxStatistics -server "chw-msg-$($_)" ;$step += 1 } | Where-Object {$_.DisconnectDate -ne $null -and $_.StorageGroupName -ne "Recovery Storage Group"} | `
 Select-Object DisplayName, ServerName, StorageGroupName, DatabaseName, @{name="TotalItemSize";Expression={$_.totalitemsize.value.ToKB()}}, ItemCount, DisconnectDate, LegacyDN
 Write-Progress -Activity "Getting 2007 disconnected mailboxes"  -Status "% Complete" -PercentComplete (($step / 5) * 100) -Id 3 

## Add a field indicating the Exchange version
for ($i = 0; $i -lt $disc03.count; $i += 1) {
	$disc03[$i] | Add-Member -MemberType NoteProperty -Name "ExchangeVersion" -Value "2003"
}
for ($i = 0; $i -lt $disc07.count; $i += 1) {
	$disc07[$i] | Add-Member -MemberType NoteProperty -Name "ExchangeVersion" -Value "2007"
}

## Combine the 2003 and 2007 information
$discMailboxes = $disc03 + $disc07

## Add caculated number of days disconnected and projected removal date
for ($i = 0; $i -lt $discMailboxes.count; $i += 1) {
	$discMailboxes[$i] | Add-Member -MemberType NoteProperty -Name DaysDisconnected -Value ($date - $discMailboxes[$i].disconnectDate).Days
	$discMailboxes[$i] | Add-Member -MemberType NoteProperty -Name RemovalDate -Value $discMailboxes[$i].DisconnectDate.AddDays(61)
}

## sort by disconnected days so oldest are show first.
$discMailboxes = $discMailboxes | Sort-Object DaysDisconnected -Descending

$disconnectedMailboxes = @()
$step = 0  ## used for the progress meter
## Check for existing (connected) mailboxes for each user with a disconnected mailbox
foreach ($dMbx in $discMailboxes) {
	Write-Progress -Activity "Checking if disconnected mailboxes have been moved" -Status "% Complete" -PercentComplete (($step / $discMailboxes.count) * 100) -Id 4 -CurrentOperation "Checking $($dMbx.displayname)"
	$mbx = Get-Mailbox $dMbx.displayname -ErrorAction SilentlyContinue -ErrorVariable ErrorVar
	if ($ErrorVar) {
	# No (connected) mailbox found
		$dMbx | Add-Member -MemberType NoteProperty -Name "MailboxMoved" -Value "No"
		$dMbx | Add-Member -MemberType NoteProperty -Name NewServername -Value $null
		$dMbx | Add-Member -MemberType NoteProperty -Name Database -Value $null
	}
	else {
	# Another mailbox found.  Therefore user has been moved
		$dMbx | Add-Member -MemberType NoteProperty -Name "MailboxMoved" -Value "Yes"
		$dMbx | Add-Member -MemberType NoteProperty -Name NewServername -Value $mbx.Servername
		$dMbx | Add-Member -MemberType NoteProperty -Name Database -Value $mbx.Database
	}
	$disconnectedMailboxes += $dMbx
	$step += 1
}
Write-Progress -Activity "Checking if disconnected mailboxes have been moved" -Status "% Complete" -PercentComplete (($step / $discMailboxes.count) * 100) -Id 4
	

##
## Save all this out to Excel too and try to prettify it some.
##

## The constants that we will use

$xlCenter = -4108
$xlCellValue = 1
$xl3TrafficLights2 = 5
$xl3Symbols = 7
$xlConditionValueNumber = 0
$xlIconSet = 6
$xlAutomatic = -4105
$xlBottom = -4107
$xlContinuous = 1
$xlThin = 2
$xlBetween = 1
$xlNotBetween = 2
$xlEqual = 3
$xlNotEqual = 4
$xlGreater = 5
$xlLess = 6
$xlGreaterEqual = 7
$xlLessEqual = 8

# Create an instance of Excel
$Excel = New-Object -comobject Excel.Application
$Excel.visible = $True 
$Workbook = $Excel.Workbooks.Add()
$Info = $Workbook.Worksheets.Item(1)
  
# Create our column headers
$Info.Cells.Item(1,1) = "Display Name"
$Info.Cells.Item(1,2) = "Server Name"
$Info.Cells.Item(1,3) = "Storage Group Name"
$Info.Cells.Item(1,4) = "Database Name"
$Info.Cells.Item(1,5) = "Total Item Size (KB)"
$Info.Cells.Item(1,6) = "Item Count"
$Info.Cells.Item(1,7) = "Disconnected Date"
$Info.Cells.Item(1,8) = "Days Disconnected"
$Info.Cells.Item(1,9) = "Removal Date"
$Info.Cells.Item(1,10) = "Exchange Version"
$Info.Cells.Item(1,11) = "Mailbox Moved"
$Info.Cells.Item(1,12) = "New Server"
$Info.Cells.Item(1,13) = "Database"
$Info.Cells.Item(1,14) = "LegacyDN"



# Make Columns wide enough for content
$info.Range("A:A").entireColumn.ColumnWidth = 28
$info.Range("B:B").entireColumn.ColumnWidth = 13
#$info.Range("B:B").HorizontalAlignment = $xlCenter 
$info.Range("C:C").entireColumn.ColumnWidth = 19
$info.Range("D:D").entireColumn.ColumnWidth = 14
$info.Range("E:E").entireColumn.ColumnWidth = 13
$info.Range("F:F").entireColumn.ColumnWidth = 10
$info.Range("G:G").entireColumn.ColumnWidth = 17
$info.Range("H:H").entireColumn.ColumnWidth = 17
$Info.Range("I:I").entireColumn.ColumnWidth = 17
$info.Range("J:J").entireColumn.ColumnWidth = 40
$info.Range("K:K").entireColumn.ColumnWidth = 16
$info.Range("L:L").entireColumn.ColumnWidth = 14
$info.Range("M:M").entireColumn.ColumnWidth = 11
$info.Range("N:N").entireColumn.ColumnWidth = 14

# Add a little formatting to the column header
$Style = $Info.UsedRange
$Style.Interior.ColorIndex = 19
$Style.Font.ColorIndex = 11
$Style.Font.Bold = $True
$Style.Borders.LineStyle = $xlContinuous
$Style.Borders.ColorIndex = 0
$Style.Borders.TintAndShade = 0
$Style.Borders.Weight = $xlThin

$bucket= $Info.Range("H:H").FormatConditions.Add($xlCellValue, $xlGreaterEqual, 60)
$Info.Range("H:H").FormatConditions.Item(1).Interior.Color = 13551615
$Info.Range("H:H").FormatConditions.Item(1).Interior.PatternColorIndex = $xlAutomatic
$Info.Range("H:H").FormatConditions.Item(1).Interior.TintAndShade = 0
$Info.Range("H:H").FormatConditions.Item(1).Font.Color = -16383844
$Info.Range("H:H").FormatConditions.Item(1).Font.TintAndShade = 0

$bucket= $Info.Range("L:L").FormatConditions.Add($xlCellValue, $xlEqual, '="No"')
$Info.Range("L:L").FormatConditions.Item(1).Interior.Color = 13551615
$Info.Range("L:L").FormatConditions.Item(1).Interior.PatternColorIndex = $xlAutomatic
$Info.Range("L:L").FormatConditions.Item(1).Interior.TintAndShade = 0
$Info.Range("L:L").FormatConditions.Item(1).Font.Color = -16383844
$Info.Range("L:L").FormatConditions.Item(1).Font.TintAndShade = 0


$bucket = $Info.Range("L:L").FormatConditions.Add($xlCellValue, $xlEqual, '="Yes"')
$Info.Range("L:L").FormatConditions.Item(2).Interior.Color = 13561798
$Info.Range("L:L").FormatConditions.Item(2).Interior.PatternColorIndex = $xlAutomatic
$Info.Range("L:L").FormatConditions.Item(2).Interior.TintAndShade = 0
$Info.Range("L:L").FormatConditions.Item(2).Font.Color = -16752384
$Info.Range("L:L").FormatConditions.Item(2).Font.TintAndShade = 0


# Data starts at row 2
$intRow = 2

# iterate over each Archive entry
foreach ($item in $disconnectedMailboxes)
#for ($i = 0;$i -le 50; $i++) 
{
 #$Archive = $evOrphans[$i]
 Write-Progress -Activity "Injecting data into Excel" -Status "% Complete" -PercentComplete ((($intRow-1) / $disconnectedMailboxes.count) * 100) -Id 5 -CurrentOperation "Injecting $($item.displayname)"
 $bucket = $Style.Cells.Item($intRow, 1).Select()
 $Style.Cells.Item($intRow, 1) = $item.DisplayName
 $Style.Cells.Item($intRow, 2) = $item.ServerName
 $Style.Cells.Item($intRow, 3) = $item.StorageGroupName 
 $Style.Cells.Item($intRow, 4) = $item.DatabaseName
 $Style.Cells.Item($intRow, 5) = $item.TotalItemSize
 $Style.Cells.Item($intRow, 6) = $item.ItemCount
 $Style.Cells.Item($intRow, 7) = $item.DisconnectDate
 $Style.Cells.Item($intRow, 8) = $item.DaysDisconnected
 $Style.Cells.Item($intRow, 9) = $item.RemovalDate
 $Style.Cells.Item($intRow, 10) = $item.LegacyDN
 $Style.Cells.Item($intRow, 11) = $item.ExchangeVersion
 $Style.Cells.Item($intRow, 12) = $item.MailboxMoved
 $Style.Cells.Item($intRow, 13) = $item.NewServername
 $Style.Cells.Item($intRow, 14) = $item.Database.name
 
 $intRow++
}
$bucket = $Style.Cells.Item(1, 1).Select()
Write-Progress -Activity "Injecting data into Excel" -Status "% Complete" -PercentComplete ((($intRow-1) / $disconnectedMailboxes.count) * 100) -Id 5
  

# Make Columns wide enough for content
$bucket = $info.Range("A:A").entireColumn.Autofit()
$bucket = $info.Range("B:B").entireColumn.Autofit()
$bucket = $info.Range("C:C").entireColumn.Autofit()
$bucket = $info.Range("D:D").entireColumn.Autofit()
$bucket = $info.Range("E:E").entireColumn.Autofit()
$bucket = $info.Range("F:F").entireColumn.Autofit()
$bucket = $info.Range("G:G").entireColumn.Autofit()
$bucket = $info.Range("H:H").entireColumn.Autofit()
$bucket = $info.Range("I:I").entireColumn.Autofit()
$bucket = $info.Range("J:J").entireColumn.Autofit()
$bucket = $info.Range("K:K").entireColumn.Autofit()
$bucket = $info.Range("L:L").entireColumn.Autofit()
$bucket = $info.Range("M:M").entireColumn.Autofit()
$bucket = $info.Range("N:N").entireColumn.Autofit()

if (Test-Path "$($pathForReport)\$($nameForReport).xlsx") {Remove-Item "$($pathForReport)\$($nameForReport).xlsx"}
$Workbook.SaveAs("$($pathForReport)\$($nameForReport).xlsx")
$Excel.Quit()


